import pymysql
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "litao021218",
    "database": "stock_data",
    "charset": "utf8mb4"
}


def create_tables():
    # 建表SQL语句
    create_table_sqls = [
        """
        CREATE TABLE IF NOT EXISTS `stock_daily_stats` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `symbol` VARCHAR(10) NOT NULL COMMENT '股票代码',
            `name` VARCHAR(50) NOT NULL COMMENT '股票名称',
            `update_date` DATE NOT NULL COMMENT '统计日期',
            `latest_price` DECIMAL(10, 2) COMMENT '最新价',
            `latest_change_percent` DECIMAL(10, 2) COMMENT '最新涨跌幅(%)',
            `day_high` DECIMAL(10, 2) COMMENT '当日最高价',
            `day_low` DECIMAL(10, 2) COMMENT '当日最低价',
            `avg_price` DECIMAL(10, 2) COMMENT '当日平均价',
            `total_volume` BIGINT COMMENT '当日总成交量(手)',
            `total_amount` BIGINT COMMENT '当日总成交额(元)',
            `update_count` INT COMMENT '当日更新次数',
            `rank` INT COMMENT '涨跌幅排名',
            `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
            UNIQUE KEY `idx_symbol_date` (`symbol`, `update_date`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='股票每日统计数据'
        """,
        """
        CREATE TABLE IF NOT EXISTS `market_daily_stats` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `stat_date` DATE COMMENT '统计日期',
            `total_stocks` INT COMMENT '股票总数',
            `market_avg_change` DECIMAL(10, 2) COMMENT '市场平均涨跌幅(%)',
            `market_total_volume` BIGINT COMMENT '市场总成交量(手)',
            `market_total_amount` BIGINT COMMENT '市场总成交额(元)',
            `up_ratio` DECIMAL(10, 4) COMMENT '上涨比例',
            `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
            UNIQUE KEY `idx_stat_date` (`stat_date`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='市场每日统计数据'
        """
    ]

    try:
        # 连接数据库
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor()

        print(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} 开始创建表...")

        # 执行每条建表SQL
        for sql in create_table_sqls:
            cursor.execute(sql)
            print(f"成功执行SQL: {sql[:50]}...")  # 打印前50个字符

        conn.commit()
        print(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} 表创建成功！")

    except Exception as e:
        print(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} 创建表出错: {e}")
    finally:
        if 'conn' in locals() and conn.open:
            cursor.close()
            conn.close()


if __name__ == "__main__":
    create_tables()